/**
 * Comments数据模型模块
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class CommentsDB {
    /**
     * @constructor
     * @private
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
    }

    /**
     * 创建CommentsDB实例对象
     * @returns {CommentsDB} CommentsDB实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new CommentsDB();
        }
        return this.instance;
    }

    /**
     * 连接数据库
     * @returns {Promise}
     */
    connect() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, (err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("connect ok.");
                }
            });
        });
    }
    /**
     * 关闭数据库
     * @returns  {Promise}
     */
    close() {
        return new Promise((resolve, reject) => {
            this.db.close((err) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("close ok.");
                }
            });
        });
    }

    /**
 * 获取指定ID的评论信息
 * @param {Number} id 评论id
 * @returns {Promise}
 */
     find(commentId) {
        return new Promise((resolve, reject) => {
            let sql="SELECT * FROM comments WHERE id= ?";
            let params = [commentId];
            this.db.get(sql, params, (err, result) => {
               
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

/**  
 * 新增评论  
  * @param {Object} comments 评论数据
 * @returns {Promise}
 */  
 add(comments) {  
    return new Promise((resolve, reject) => {  
        let sql = `  
        INSERT INTO comments (user_id,course_id, parent_comment_id, content, created_at, updated_at)  
VALUES (?, ?, ?, ?, ? ,?);  
    `; 
        let params = [
            comments.userId,   
            comments.courseId,  
            comments.parentCommentId, 
            comments.content,  
            comments.createdAt || Date.now(),  
            comments.updatedAt || Date.now()  
        ];  
        this.db.run(sql, params, function (err) {  
            if (err) {  
                util.err(err);  
                reject(err);  
            } else {
                util.log(this);  
                resolve(this.lastID); //插入的数据的自增ID
            }  
        });  
    });  

}

/**  
 * 删除评论  
 * @param {Number} commentId 评论ID  
 * @returns {Promise}  
 */  
 remove(commentId) {  
    return new Promise((resolve, reject) => {  
        let sql = "DELETE FROM comments WHERE id = ?";  
        let params = [commentId];  
        this.db.run(sql, params,function (err, result){  
            if (err) {  
                util.err(err);  
                reject(err);  
            } else {  
                resolve(this.changes); // 使用 result.changes 而不是 this.changes  
            }  
        });  
    });  
}

/**
 * 修改评论信息
 * @param {Object} comments 评论数据
 * @returns  {Promise}
 */
 update(comments) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE comments SET
 content = ?
        WHERE id = ?;`;
        let params = [     
            comments.content,  
            comments.id,  
        ];  
        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes); //影响的记录数
            }
        });
    });
}

 /**  
 * 获取指定课程评论总数  
 * @param {Number} courseId 课程ID  
 * @returns {Promise<number>}  
 */  
getCount(courseId) {  
    return new Promise((resolve, reject) => {  
        let sql = "SELECT count(1) AS total FROM comments WHERE course_id = ?";  
        let params = [courseId];  
        this.db.get(sql, params, function (err, result) {   
            if (err) {  
                util.err(err);  
                reject(err);  
            } else {  
                resolve(result.total);  
                }    
        });
    });   
 
};

/**
* 获取所有的评论数量
* @returns  {Promise}
*/
getCountAll() {
    return new Promise((resolve, reject) => {
         // 定义SQL查询语句，用于统计订单数量
        let sql = "SELECT count(1) AS total FROM comments";
        // 使用数据库查询方法，执行SQL查询
        this.db.get(sql, function (err, result) {
            if (err) {// 如果查询过程中发生错误，则输出错误信息
                util.err(err);
                reject(err);
            } else {// 如果查询成功，则将查询结果中的total字段作为Promise的resolve值返回
                resolve(result);
            }
        });
    });
}

/**
 * 获取评论信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
 findAll(limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
        let sql =`SELECT 
cm.id AS id,
cm.content AS content,
cm.created_at AS createdAt,
cm.updated_at AS updatedAt,
cm.user_id AS userId,
cm.useful AS  useful,
cm.useless AS useless,
cm.course_id AS courseId,
c.id AS course_id,
c.teacher_id AS  teacherId,
c.name AS Coursename,
c.subject AS subject,
c.category AS category
FROM comments cm
JOIN courses c ON cm.course_id = c.id        
        ` ;  
            let params = [];  
  
            if (limit === -1 || offset !== -1) {  
                sql += "ORDER BY id";  
            } else if (offset === -1) {   
                sql += "ORDER BY id LIMIT ?";  
                params.push(limit);  
            } else { 
                // 既有limit又有offset  
                sql += "ORDER BY id LIMIT ? OFFSET ?";  
                params.push(limit);  
                params.push(offset);  
            }  
        this.db.all(sql, params, (err, result) => {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

/**
     * 更新评论有用
     * @param {Number} commentId 评论数据
     * @returns {Promise}
     */
 updateUseful(commentId) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE comments SET
                    useful = useful + 1
                    WHERE id = ?;`;

        let params = [commentId];

        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes);//影响的记录数
            }
        });
    });
};

/**
     * 更新评论无用
     * @param {Number} commentId 评论id
     * @returns {Promise}
     */
 updateUseless(commentId) {
    return new Promise((resolve, reject) => {
        let sql = `UPDATE comments SET
                    useless = useless + 1
                    WHERE id = ?;`;

        let params = [commentId];

        this.db.run(sql, params, function (err, result) {
            if (err) {
                util.err(err);
                reject(err);
            } else {
                resolve(this.changes);//影响的记录数
            }
        });
    });
};

}
    

module.exports = CommentsDB;